package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import POJO.ContainerObject;
import java.sql.PreparedStatement;

/**
 *
 * @author Sibrand
 */
public class ContentContainerManager extends DB {

    public ContentContainerManager(String dbName, String userName, String password) {
        super(dbName, userName, password);
    }

    public ContentContainerManager(Connection c) {
        conn = c;
    }

    public String getArrival(int containerId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT arrivalDate FROM ContentContainers WHERE containerID= ?");
                pstmt.setInt(1, containerId);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return rs.getString("arrivalDate");
                } else {
                    return "";
                }
            } catch (SQLException ex) {
                return "";
            }
        } else {
            return "";
        }
    }

    public int[] getAllContentContainersOfTripForOwners(int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE tripID= ? ORDER BY arrivalDate DESC");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }
                return result;
            } catch (SQLException ex) {
                return new int[0];
            }
        } else {
            return new int[0];
        }
    }

    public int[] getAllContentContainersOfTripForFamily(int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE tripID= ? AND (viewableBy=\"FRIEND\" OR viewableBy=\"FAMILY\" OR viewableBy=\"EVERYBODY\") ORDER BY arrivalDate DESC");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }
                return result;
            } catch (SQLException ex) {
                return new int[0];
            }
        } else {
            return new int[0];
        }
    }

    public int[] getAllContentContainersOfTripForFriends(int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE tripID= ? AND (viewableBy=\"FRIEND\" OR viewableBy=\"EVERYBODY\") ORDER BY arrivalDate DESC");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }
                return result;
            } catch (SQLException ex) {
                return new int[0];
            }
        } else {
            return new int[0];
        }
    }

    public int[] getAllContentContainersOfTripForEverybody(int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE tripID= ? AND viewableBy=\"EVERYBODY\" ORDER BY arrivalDate DESC");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }
                return result;
            } catch (SQLException ex) {
                return new int[0];
            }
        } else {
            return new int[0];
        }
    }

    public boolean changeViewableBy(int containerId, String level) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE ContentContainers SET viewableBy= ? WHERE containerID= ?");
                pstmt.setInt(2, containerId);
                pstmt.setString(1, level);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }
        } else {
            return false;
        }
    }

    public boolean addContentContainer(String creator, int tripId, String viewableBy, String date, double north, double east, String location) {
        TripManager t = new TripManager(conn);
        if (isConnected() && usernameExists(creator) && t.isOwnerOf(tripId, creator)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO ContentContainers (northCoord, eastCoord, createdBy, tripID, viewableBy, arrivalDate, location) VALUES (?, ?, ?, ?, ?, ?, ?)");
                pstmt.setDouble(1, north);
                pstmt.setDouble(2, east);
                pstmt.setInt(4, tripId);
                pstmt.setString(3, creator);
                pstmt.setString(5, viewableBy);
                pstmt.setString(6, date);
                pstmt.setString(7, location);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }
        }
        return false;
    }

    public boolean deleteContentContainer(int containerId) {
        TripManager t = new TripManager(DBInfo.getConnection());
        if (isConnected()) {

            try {
                PreparedStatement pstmt = conn.prepareStatement("DELETE FROM Comments WHERE replyOnContentID IN (SELECT contentID FROM Contents WHERE containerID IN (SELECT containerID FROM ContentContainers WHERE containerID = ?))");
                pstmt.setInt(1, containerId);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM Contents WHERE containerID IN (SELECT containerID FROM ContentContainers WHERE containerID = ?)");
                pstmt.setInt(1, containerId);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM ContentContainers WHERE containerID= ?");
                pstmt.setInt(1, containerId);
                pstmt.executeUpdate();

                return true;
            } catch (SQLException ex) {
                return false;
            }
        }
        return false;
    }

    public int containerBelongsToTrip(int containerID) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT tripID FROM ContentContainers WHERE containerID= ?");
                pstmt.setInt(1, containerID);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getInt(1);
                } else {
                    return -1;
                }
            } catch (SQLException ex) {
                return -1;
            }
        } else {
            return -1;
        }
    }

    public int[] getContainersByCoords(int north, int east) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE northCoord= ? AND eastCoord= ?");
                pstmt.setInt(1, north);
                pstmt.setInt(2, east);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }
                return result;
            } catch (SQLException ex) {
                return new int[0];
            }
        } else {
            return new int[0];
        }
    }

    public ContainerObject getContainerLocation(int containerID) {
        ContainerObject result = null;

        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT eastCoord, northCoord, location, tripID, arrivalDate FROM ContentContainers WHERE containerID= ?");
                pstmt.setInt(1, containerID);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    result = new ContainerObject();
                    result.east = rs.getFloat("eastCoord");
                    result.north = rs.getFloat("northCoord");
                    result.id = containerID;
                    result.location = rs.getString("location");
                    result.tripID = rs.getInt("tripID");
                    result.arrival = rs.getString("arrivalDate");
                }
            } catch (SQLException ex) {
            }
        }

        return result;
    }

    public boolean containerIsViewableByUser(int containerID, String username) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE containerID = ? AND viewableBy=\"EVERYBODY\"");
                pstmt.setInt(1, containerID);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) // Array has size >= 1
                {
                    return true;
                }
                if (username != null) {
                    if (containerIsOwnedByUser(containerID, username)) {
                        return true;
                    }
                    TripManager tm = new TripManager(conn);
                    if (tm.isFamilyOf(containerID, username)) {
                        pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE containerID = ? AND (viewableBy = \"FAMILY\" OR viewableBy = \"FRIENDS\"");
                        pstmt.setInt(1, containerID);
                        if (rs.next()) {
                            return true;
                        }
                    }

                    if (tm.isFriendOf(containerID, username)) {
                        pstmt = conn.prepareStatement("SELECT containerID FROM ContentContainers WHERE containerID = ? AND viewableBy = \"FRIENDS\"");
                        pstmt.setInt(1, containerID);
                        if (rs.next()) {
                            return true;
                        }
                    }
                }

                return false;
            } catch (SQLException ex) {
                return false;
            }
        } else {
            return false;
        }
    }

    public boolean containerIsOwnedByUser(int containerID, String username) {
        if (username != null) {
            int tripID = containerBelongsToTrip(containerID);
            if (tripID > -1) {
                TripManager tm = new TripManager(conn);
                return tm.isOwnerOf(tripID, username);
            }
        }
        return false;
    }
    /*
     * DEZE CODE IS NIET LANGER NODIG, MAAR WORDT BIJGEHOUDEN VOOR ALS WE NOG IETS GELIJKAARDIGS
     * MOETEN DOEN
     * 
     * /    
    
    public ContentObject[] GetContentsForContainer (int containerID) {
    ContentObject[] output = new ContentObject[GetNumContentsForContainer(containerID)];
    int current = 0;
    
    if (isConnected()) {
    try {
    Statement statement = conn.createStatement();
    String query = "SELECT contain.containerID, northCoord, eastCoord, createdBy, createdOn, tripID, content.viewableBy, " +
    "contentID, type, contentLink, commentsAllowed FROM Contents content, ContentContainers contain " +
    "WHERE containerID = " + containerID + " AND ";
    ResultSet rs = statement.executeQuery(query);
    
    while (rs.next()) {
    output[current].commentsAllowed = rs.getBoolean("commentsAllowed");
    output[current].containerID = rs.getInt("containerID");
    output[current].contentID = rs.getInt("contentID");
    rs.getCharacterStream("contentType").read(output[current].contentType);
    }
    } catch (SQLException ex) {
    return null;
    }
    }
    
    public int GetNumContentsForContainer(int containerID) {
    if (isConnected()) {
    try {
    Statement statement = conn.createStatement();
    String query = "SELECT COUNT(*) FROM Contents WHERE containerID = " + containerID;
    ResultSet rs = statement.executeQuery(query);
    if (rs.next()) {
    return rs.getInt(1);
    } else
    return -1;
    } catch (SQLException ex) {
    return -1;
    }
    } else
    return -1;
    }
    
    public int GetNumContentsForContainers(int ids[]) {
    int output = 0;
    int toAdd;
    
    for (int i = 0; i < ids.length && output > -1; i++)
    if ((toAdd = GetNumContentsForContainer(ids[i])) != -1)
    output += toAdd;
    else
    output = -1;
    
    return output;
    } */
}
